package com.may.repository;

import com.may.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @program: dubbo-nacos
 * @description: 泛型是 实体，主键类型
 * @author: Bobby.Ma
 * @create: 2020-08-23 12:20
 **/
public interface UserRepository extends JpaRepository<User,Integer>, JpaSpecificationExecutor<User> {

    // 根据 uname 查询
    User getByUname(String uname);

    //where uname like ?% and id > ?
    List<User> getByUnameStartingWithAndIdGreaterThan(String uname,Integer id);


    //where uname like %?
    List<User> getByUnameEndingWith(String uname);

    //where age in(17,18) or gender = 1
    List<User> getByAgeInOrGender(List<Integer> ages,Integer gender);


    //from user u left join user_info i on u.info_id = i.id where i.id = ?
    User getByUserInfo_id(Integer infoId);

    //这里实际上写的是 jpql，类似于 hql。不能识别sql语句
    @Query("select u from User u where u.id = (select max(uu.id) from User uu)")
    User getMaxIdUser();

    @Query("from User where uname like ?1% and gender = ?2")
    List<User> getByParams(String uname,Integer gender);

    @Query("from User where uname like :uname%")
    List<User> getByParams2(@Param("uname") String uname);

    //使用原生sql查询
    @Query(value = "select * from user order by id desc limit 3",nativeQuery = true)
    List<User> getByNativeSql();

    //可以通过自定义 jsql 完成 update、delete 操作，但不支持 insert
    @Modifying//必须加这个注解，以通知springData，同时还要加事务，否则会报错提示 @Query 不支持 DML语言。 默认情况下springData每个方法上都有事务，但都是只读事务，它们不能完成update、delete 操作
    @Query(value = "update User set uname = ?1 where id = ?2")
    void updateUnameById(String uname, Integer id);
}
